
if exists (select 1 from sysobjects where name = 'get_personnotintermin' and type = 'P')
begin
   drop procedure get_personnotintermin
   print 'Procedure: get_personnotintermin deleted ...'
end
go
create procedure get_personnotintermin(
  @planid                int           = 28,
  @planterminid          int           = 1
)
as
begin
  set nocount on

  declare @datevon datetime
  
  select @datevon = Von from ArbeitsPlan where PlanID = @planid
  
  select p.PrsId,
         Vorname,
         Nachname, 
         a.AbteilungID, 
         Anzeigename=Nachname + ' '+ Vorname + ' - ' + a.Name
    from Person p 
   inner join PersonalAbteilung pa 
      on pa.PrsId = p.PrsId 
   inner join Abteilung a 
      on a.AbteilungID = pa.AbteilungID 
   where p.PrsId not in (select tp.PrsID 
                           from Terminplan tp 
                          where tp.PlanTerminID = @planterminid 
                            and tp.PlanID = @planid) 
     and PersonTyp = 1
     and (@datevon >= Von and @datevon<= Bis)
   order by a.AbteilungID,p.PrsId
   

end
go
print 'Procedure: get_personnotintermin done ...'
go
grant exec on get_personnotintermin to prsadmins with grant option
go
grant exec on get_personnotintermin to prsusers
go

